package com.tiancikeji.zaoke.app.dao.card.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.tiancikeji.zaoke.app.dao.card.CardDao;
import com.tiancikeji.zaoke.app.model.card.Card;
import com.tiancikeji.zaoke.app.model.card.CardPromotion;

public class CardDaoImpl extends JdbcDaoSupport implements CardDao{

	private static final String TABLE = "`zaoke`.`card`";

	private static final String QUERY_VIEW = " `id`, `user_id`, `card_id`, `card_code`, `add_userid`, `add_time` ";

	private static final String TABLE_PROMOTION = "`zaoke`.`card_promotion`";

	private static final String QUERY_VIEW_PROMOTION = " `id`, `card_id`, `money`, `effect_date`, `status`, `bind_user_id`, `bind_time` ";

	public long addCard(final Card card) {
		final String sql = "insert into " + TABLE + " (`user_id`, `card_id`, `card_code`, `add_userid`) values(?, ?, ?, ?)";
		KeyHolder holder = new GeneratedKeyHolder();
		int count = getJdbcTemplate().update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setObject(1, card.getUserId());
				ps.setObject(2, card.getCardId());
				ps.setObject(3, card.getCardCode());
				ps.setObject(4, card.getAddUserId());
				return ps;
			}
		}, holder);
		
		if(count > 0 && holder.getKey() != null)
			return holder.getKey().longValue();
		
		return 0;
	}

	public Card getCardByCardId(String cardId, long userId) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE + " where `card_id` = ? and `user_id` = ?";
		
		List<Card> cards = getJdbcTemplate().query(sql, new Object[]{cardId, userId}, new RowMapper<Card>() {

			public Card mapRow(ResultSet rs, int rowNum) throws SQLException {
				Card card = new Card();
				card.setId(rs.getLong("id"));
				card.setUserId(rs.getLong("user_id"));
				card.setCardId(rs.getString("card_id"));
				card.setCardCode(rs.getString("card_code"));
				card.setAddUserId(rs.getLong("add_userid"));
				card.setAddTime(rs.getDate("add_time"));
				return card;
			}
		});
		
		if(cards != null && cards.size() > 0)
			return cards.get(0);
		
		return null;
	}

	@Override
	public CardPromotion getActiveCardPromotion(String cardId) {
		String sql = "select " + QUERY_VIEW_PROMOTION + " from " + TABLE_PROMOTION + " where `card_id` = ? and `money` > 0 and `effect_date` > now() and `status` = ? and `bind_user_id` is null order by `effect_date`";
		
		List<CardPromotion> cards = getJdbcTemplate().query(sql, new Object[]{cardId, CardPromotion.STATUS_UNUSED}, new RowMapper<CardPromotion>() {

			public CardPromotion mapRow(ResultSet rs, int rowNum) throws SQLException {
				CardPromotion card = new CardPromotion();
				card.setId(rs.getLong("id"));
				card.setCardId(rs.getString("card_id"));
				card.setMoney(rs.getDouble("money"));
				card.setEffectDate(rs.getDate("effect_date"));
				card.setStatus(rs.getInt("status"));
				return card;
			}
		});
		
		if(cards != null && cards.size() > 0)
			return cards.get(0);
		
		return null;
	}

	@Override
	public boolean updateCardPromotion(long id, long userId) {
		String sql = "update " + TABLE_PROMOTION + " set `status` = ?, `bind_user_id` = ?, `bind_time` = now() where `id` = ? and `status` = ?";
		return getJdbcTemplate().update(sql, new Object[]{CardPromotion.STATUS_USED, userId, id, CardPromotion.STATUS_UNUSED}) > 0;
	}
}
